9  Condition Control

9.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

• om
• ex

9.2 Conditions

Quite often, you would like different things happen based on different values/when some logics return TRUE/FALSE. Each of these comparison/logic test is called a condition. After each condition, you need to specify what behaviour needs to happen.

Take a look at the following example, when we need to create a friendly label for different repayment terms for invoices.

CASE…WHEN…THEN…END

SELECT invoice_number, terms_id,
    CASE terms_id
        WHEN 1 THEN 'Net due 10 days'
        WHEN 2 THEN 'Net due 20 days'
        WHEN 3 THEN 'Net due 30 days'
        WHEN 4 THEN 'Net due 60 days'
        WHEN 5 THEN 'Net due 90 days'
    END AS terms
FROM invoices

Displaying records 1 - 15

invoice_number	terms_id	terms
989319-457	3	Net due 30 days
263253241	3	Net due 30 days
963253234	3	Net due 30 days
2-000-2993	3	Net due 30 days
963253251	3	Net due 30 days
963253261	3	Net due 30 days
963253237	3	Net due 30 days
125520-1	1	Net due 10 days
97/488	3	Net due 30 days
263253250	3	Net due 30 days
963253262	3	Net due 30 days
I77271-O01	2	Net due 20 days
111-92R-10096	2	Net due 20 days
25022117	4	Net due 60 days
P02-88D77S7	3	Net due 30 days

Another example, we will create a custom payment_owned column by harnessing information from different types of payment statuses among the vendors:

SELECT invoice_id,
  vendor_id,
  invoice_due_date,
  payment_date,
  invoice_total,
  payment_total,
  credit_total,
  CASE  
      WHEN payment_date > invoice_due_date THEN invoice_total - payment_total - credit_total
      WHEN invoice_total - payment_total - credit_total = 0 THEN payment_total
      WHEN invoice_total - payment_total - credit_total < 0 THEN invoice_total - payment_total - credit_total
      ELSE 0
      END AS payment_owed
FROM invoices

Displaying records 1 - 15

invoice_id	vendor_id	invoice_due_date	payment_date	invoice_total	payment_total	credit_total	payment_owed
1	122	2022-05-08	2022-05-07	3813.33	3813.33	0	3813.33
2	123	2022-05-10	2022-05-14	40.20	40.20	0	0.00
3	123	2022-05-13	2022-05-09	138.75	138.75	0	138.75
4	123	2022-05-16	2022-05-12	144.70	144.70	0	144.70
5	123	2022-05-16	2022-05-11	15.50	15.50	0	15.50
6	123	2022-05-16	2022-05-21	42.75	42.75	0	0.00
7	123	2022-05-21	2022-05-22	172.50	172.50	0	0.00
8	89	2022-05-04	2022-05-01	95.00	95.00	0	95.00
9	121	2022-05-24	2022-05-21	601.95	601.95	0	601.95
10	123	2022-05-24	2022-05-22	42.67	42.67	0	42.67
11	123	2022-05-25	2022-05-20	42.50	42.50	0	42.50
12	96	2022-05-16	2022-05-13	662.00	662.00	0	662.00
13	95	2022-05-20	2022-05-23	16.33	16.33	0	0.00
14	115	2022-06-10	2022-06-10	6.00	6.00	0	6.00
15	48	2022-06-02	2022-05-30	856.92	856.92	0	856.92

Last example, Case when statement can be directly embedded into summary query. This can make conditional summary query so much simpler without involving any Common Table Expressions or subqueries.

SELECT vendor_id,
  SUM(CASE  
      WHEN payment_date > invoice_due_date THEN invoice_total - payment_total - credit_total
      WHEN invoice_total - payment_total - credit_total = 0 THEN payment_total
      WHEN invoice_total - payment_total - credit_total < 0 THEN invoice_total - payment_total - credit_total
      ELSE 0
      END) AS total_payment_owed
FROM invoices
GROUP BY vendor_id

Displaying records 1 - 15

vendor_id	total_payment_owed
34	0.00
37	116.00
48	856.92
72	21842.00
80	0.00
81	936.93
82	0.00
83	1575.00
86	2433.00
88	0.00
89	95.00
90	356.48
94	0.00
95	39.77
96	662.00

IF

This is quite similar to the Microsoft Excel IF function. IF is less versatile than CASE WHEN statement because it only examines 1 set of logics. However, IF is effective to write.

Here, we create flag column for vendor from City of Fresno:

SELECT vendor_name, 
  IF(vendor_city = 'Fresno', 'Yes', 'No') AS is_city_fresno
FROM vendors

Displaying records 1 - 15

vendor_name	is_city_fresno
US Postal Service	No
National Information Data Ctr	No
Register of Copyrights	No
Jobtrak	No
Newbrige Book Clubs	No
California Chamber Of Commerce	No
Towne Advertiser’s Mailing Svcs	No
BFI Industries	Yes
Pacific Gas & Electric	No
Robbins Mobile Lock And Key	Yes
Bill Marvin Electric Inc	Yes
City Of Fresno	Yes
Golden Eagle Insurance Co	No
Expedata Inc	Yes
ASC Signs	Yes

IFNULL

The following allows to replace NULL values if there are any in the field. Very useful before you use the field in WHERE or HAVING, because they exclude NULL values when comparing.

Example, we want to create a label for all vendors that haven’t paid us.

First, let’s see the ones without any payment date just yet:

SELECT *
FROM invoices
WHERE payment_date IS NULL
LIMIT 15

11 records

invoice_id	vendor_id	invoice_number	invoice_date	invoice_total	payment_total	credit_total	terms_id	invoice_due_date	payment_date
89	72	39104	2022-07-10	85.31	0	0	3	2022-08-09	NA
94	123	963253264	2022-07-18	52.25	0	0	3	2022-08-17	NA
98	83	31361833	2022-07-21	579.42	0	0	2	2022-08-10	NA
99	123	263253268	2022-07-21	59.97	0	0	3	2022-08-20	NA
100	123	263253270	2022-07-22	67.92	0	0	3	2022-08-21	NA
101	123	263253273	2022-07-22	30.75	0	0	3	2022-08-21	NA
102	110	P-0608	2022-07-23	20551.18	0	1200	3	2022-08-22	NA
105	106	9982771	2022-07-24	503.20	0	0	3	2022-08-23	NA
110	80	134116	2022-07-28	90.36	0	0	2	2022-08-17	NA
112	110	0-2436	2022-07-31	10976.06	0	0	3	2022-08-30	NA
113	37	547480102	2022-08-01	224.00	0	0	3	2022-08-31	NA

Let’s see the result of our SQL application.

SELECT payment_date,
       IFNULL(payment_date, 'No Payment') AS new_date
FROM invoices
ORDER BY payment_date
LIMIT 15

Displaying records 1 - 15

payment_date	new_date
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
2022-05-01	2022-05-01
2022-05-07	2022-05-07
2022-05-09	2022-05-09
2022-05-11	2022-05-11

COALESCE

COALESCE(): Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

IFNULL vs COALESCE:

The main difference between the two is that IFNULL function takes two arguments and returns the first one if it’s not NULL or the second if the first one is NULL.

COALESCE function can take two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are null.

The following example has the same effect as IFNULL example above.

SELECT payment_date,
  COALESCE(payment_date, 'No Payment') AS new_date
FROM invoices
WHERE payment_date IS NULL

11 records

payment_date	new_date
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment
NA	No Payment

The following example fully demonstrate how COALESCE works when multiple conditions are passed in:

SELECT vendors.vendor_id,
  vendor_phone,
  vendor_name,
  vendor_state,
  invoice_id, 
  invoice_date,
  invoice_total,
  COALESCE(invoice_id, vendor_phone, invoice_total, 'No invoice', 'No vendor phone number','No invoice total') AS NEW_COL
FROM vendors LEFT JOIN invoices ON invoices.vendor_id = vendors.vendor_id

Displaying records 1 - 15

vendor_id	vendor_phone	vendor_name	vendor_state	invoice_id	invoice_date	invoice_total	NEW_COL
1	(800) 555-1205	US Postal Service	WI	NA	NA	NA	(800) 555-1205
2	(301) 555-8950	National Information Data Ctr	DC	NA	NA	NA	(301) 555-8950
3	NA	Register of Copyrights	DC	NA	NA	NA	No invoice
4	(800) 555-8725	Jobtrak	CA	NA	NA	NA	(800) 555-8725
5	(800) 555-9980	Newbrige Book Clubs	NJ	NA	NA	NA	(800) 555-9980
6	(916) 555-6670	California Chamber Of Commerce	CA	NA	NA	NA	(916) 555-6670
7	NA	Towne Advertiser’s Mailing Svcs	CA	NA	NA	NA	No invoice
8	(559) 555-1551	BFI Industries	CA	NA	NA	NA	(559) 555-1551
9	(800) 555-6081	Pacific Gas & Electric	CA	NA	NA	NA	(800) 555-6081
10	(559) 555-9375	Robbins Mobile Lock And Key	CA	NA	NA	NA	(559) 555-9375
11	(559) 555-5106	Bill Marvin Electric Inc	CA	NA	NA	NA	(559) 555-5106
12	(559) 555-9999	City Of Fresno	CA	NA	NA	NA	(559) 555-9999
13	NA	Golden Eagle Insurance Co	CA	NA	NA	NA	No invoice
14	(559) 555-9586	Expedata Inc	CA	NA	NA	NA	(559) 555-9586
15	NA	ASC Signs	CA	NA	NA	NA	No invoice